package dm;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import bean.invoice.Invoice;
import bean.shoppingCard.ShoppingCart;
import bean.shoppingCard.ShoppingProduct;
import bean.user.SessionUser;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import database.DBConnection;

public class InvoiceDM {

	public Invoice printInvoice(SessionUser user) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		ResultSet res = null;
		ShoppingCart card = new ShoppingCart();
		Invoice invoice = new Invoice(0, 0, null, null);
		try {
			
			statement = con.prepareCall("{?=call INVOICE_PKG.GET_SHOP_CARD(?)}");
			statement.registerOutParameter(1, OracleTypes.CURSOR);
			statement.setInt(2, user.getUserId());
			statement.execute();
			OracleCallableStatement ocstm = (OracleCallableStatement) statement;
			res = ocstm.getCursor(1);
			
			while(res.next()){
				ShoppingProduct product = new ShoppingProduct(res.getInt(2),res.getString(1),res.getString(4),res.getDouble(3),res.getInt(5));
				card.getProducts().add(product);
			}
			statement.close();
			
			statement = con.prepareCall("{?=call INVOICE_PKG.CREATE_PAYMENT(?,?)}");
			statement.registerOutParameter(1, OracleTypes.NUMBER);
			invoice.setTotal(card.calcTotal());
			statement.setDouble(2,invoice.getTotal());
			statement.setInt(3, user.getUserId());
			statement.execute();
			int payId = 0;
			payId = statement.getInt(1);
			if(payId > 0){
				invoice.setUser(user);
				invoice.setInvoiceNo(payId);
			}
			System.out.println("pay"+payId);
			statement.close();
			
			for(ShoppingProduct p : card.getProducts()){
				
				statement = con.prepareCall("{call INVOICE_PKG.CREATE_PRODUCT_SELL(?,?,?)}");
				statement.setInt(1,payId);
				statement.setInt(2,p.getId());
				statement.setInt(3, p.getCount());
				statement.execute();
				statement.close();
			}
			invoice.setShoppingCard(card);
			
			statement.close();
			DBConnection.closeConnection(con);
			return invoice;
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}
}
